CREATE TABLE roles
(
    id   BIGINT      NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(50) NOT NULL UNIQUE,
    CONSTRAINT pk_role PRIMARY KEY (id)
);

CREATE TABLE users
(
    id        BIGINT       NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    user_name VARCHAR(50)  NOT NULL UNIQUE,
    password  VARCHAR(255) NOT NULL,
    email     VARCHAR(255) NOT NULL UNIQUE,
    CONSTRAINT pk_user PRIMARY KEY (id)
);

CREATE TABLE user_role
(
    role_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    CONSTRAINT pk_user_role PRIMARY KEY (role_id, user_id),
    CONSTRAINT fk_user_role_on_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_role_on_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE refresh_tokens
(
    id          BIGINT       NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    user_id     BIGINT       NOT NULL,
    token_value VARCHAR(255) NOT NULL UNIQUE,
    expire_at   TIMESTAMP    NOT NULL,
    CONSTRAINT fk_refresh_tokens_on_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

INSERT INTO roles (name)
VALUES ('ROLE_USER'),
       ('ROLE_ADMIN');

INSERT INTO users (user_name, password, email)
VALUES ('user', '$2a$12$5CRiwtHPnPp4mM2Yi0qJSedpd3QNDWAH.vFIQOAGgSfncw60uOjua', 'user@domain.com'),
       ('admin', '$2a$12$Q5qVXBrBpF9FMJyK7b8fnOyuHVxgKwIfFscOySl5JhUFX49F/Q0W2', 'admin@domain.com');

INSERT INTO user_role
VALUES (1, 1),
       (2, 2);